package com.transino.bop.datasource;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.inject.Singleton;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.ejb.HibernateEntityManager;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service("kanas.data.service.sqlexecutor")
@Singleton
public class SqlExecutorServiceImpl implements SqlExecutorService{
	
	@PersistenceContext
	private EntityManager manager;
	
	@SuppressWarnings("unchecked")
	@Transactional
	public List<Map<String,Object>> executSelectSql(String sql, Map<String,Object> parameterMap, Integer startNum, Integer length) {
		List<Map<String,Object>> result = null;
		HibernateEntityManager hem = manager.unwrap(HibernateEntityManager.class);
		Session session = hem.getSession();
		SQLQuery query = session.createSQLQuery(sql);
		if(parameterMap != null && parameterMap.size()>0) {
			for(String paramKey:parameterMap.keySet()) {
				query.setParameter(paramKey, parameterMap.get(paramKey));
			}
		}
		query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
		if(startNum != null) query.setFirstResult(startNum);
		if(length != null) query.setMaxResults(length);
		result = query.list();
		return result;
	}
	
	@Transactional
	public String executSqlGetJson(List<Map<String,Object>> result) {
		String json = "{\"items\":";
		json = json + formatGridDataToStr(result);
		json = json + ",\"label\":\"label\",\"identifier\":\"id\"}";
		return json;
	}
	
	@Transactional
	public int executSql(String sql,Map<String,Object> parameterMap) throws SQLException  {
		Query query = manager.createNativeQuery(sql);
		if(parameterMap != null) {
			for(String paramKey:parameterMap.keySet()){
				query.setParameter(paramKey, parameterMap.get(paramKey));
			}
		}
		return query.executeUpdate();
	}

	
	private String formatGridDataToStr(List<Map<String, Object>> data){
		StringBuffer strBuff=new StringBuffer();
		Set<String> keySet = null;
		Object[] keys =null;
		strBuff.append("[");
		int num=0;
		for(Map<String,Object> m:data){
			num++;
			keySet = m.keySet();
			keys = keySet.toArray();
			strBuff.append("{");
			for(int i=0;i<m.size()-1;i++){
				strBuff.append('"');
				strBuff.append(keys[i]).append('"');
				strBuff.append(":");
				if(m.get(keys[i])!=null){
					strBuff.append('"'+m.get(keys[i]).toString()+'"');
				}else{
					strBuff.append('"').append('"');
				}
				
				strBuff.append(",");
			}
			if(m.size()-1>=0){
				strBuff.append('"');
				strBuff.append(keys[m.size()-1]).append('"');
				strBuff.append(":");
				if(m.get(m.size()-1)!=null){
					strBuff.append('"'+m.get(m.size()-1).toString()+'"');
				}else{
					strBuff.append('"').append('"');
				}
			}
			
			if(num==data.size()){
				strBuff.append("}");
			}else{
				strBuff.append("},");
			}
		}
		strBuff.append("]");
		return strBuff.toString();
	}
	
	public EntityManager getManager() {
		return manager;
	}

	public void setManager(EntityManager manager) {
		this.manager = manager;
	}
}
